--�����ۺ���

--1. ������10�֣�
--������±�Author�ṹ����������д������������SQL��䣺

CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(50) NOT NULL,
   LastName  nvarchar(50) NOT NULL,
   UpdatedTime  datetime NOT NULL
 )

--��FirstName����Ψһ����suniq_idx_firtname����LastName������ͨ����idx_lastname

create unique index suniq_idx_firtname on Author(FirstName)
go
create index idx_lastname on Author(LastName)
go

--2. ������15�֣�
--����һ��������trg_AuditLog������Employees���в���һ�����ݵ�ʱ�򣬴���������ص����ݵ�AuditLog�С�


CREATE TABLE Employees---- ְԱ��
(
   Id INT PRIMARY KEY NOT NULL identity,
   Name nvarchar(80) NOT NULL,
   Age INT NOT NULL,
   Address NVARCHAR(50),
   SALARY decimal(18,2)
);
go

CREATE TABLE AuditLog---- �����־��
(
   Id int primary key not null identity,
   NAME TEXT NOT NULL,
   Salary decimal(18,2)
);
go

--��̨����Employees����һ������:
--INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
--Ȼ���AuditLog����ʹ�ò�ѯ���:
--select * from AuditLog;ff

--�����
--Paul | 20000.00
create trigger trg_AuditLog on Employees
after insert
as

INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
begin
	select * from AuditLog;
end
go
--3. ������10�֣�
--���Author��������ͼvw_Author��ֻ����FirstName�Լ�LastName���У���������������������FirstNameΪv_FirstName��LastName�޸�Ϊv_LastName��

CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(50) NOT NULL,
   LastName  nvarchar(50) NOT NULL,
   UpdatedTime  datetime NOT NULL
)
go
--��̨�����2������:
insert into Author (FirstName,LastName,UpdatedTime) values ('PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('NICK', 'WAHLBERG', '2006-02-15 12:34:33');
go
--��ѯ��ͼ��select * from vw_Author;
--�����
--['first_name_v', 'last_name_v']
--PENELOPE|GUINESS
--NICK|WAHLBERG
create view vw_Author as 
select FirstName as v_FirstName,LastName as v_LastName
from 
Author
go
select * from vw_Author;
go
--4. ������10�֣�
--��ҳ��ѯEmployees����ÿ5��һҳ�����ص�2ҳ������

CREATE TABLE Employees
(
   Id int primary key NOT NULL identity,
   EmployeeCode nvarchar(80),
   Birthday date NOT NULL,
   FirstName nvarchar(14) NOT NULL,
   LastName nvarchar(16) NOT NULL,
   Gender char(1) NOT NULL,
   HireDate date NOT NULL
);
go
--����һЩ���ݣ�
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10001','1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10002','1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10003','1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10004','1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10005','1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10006','1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10007','1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10008','1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10009','1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10010','1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10011','1953-11-07','Mary','Sluis','F','1990-01-22');
go
--��ѯ��5��ÿҳ����2ҳ�ļ�¼�������
--6|10006|1953-04-20|Anneke|Preusig|F|1989-06-02
--7|10007|1957-05-23|Tzvetan|Zielinski|F|1989-02-10
--8|10008|1958-02-19|Saniya|Kalloufi|M|1994-09-15
--9|10009|1952-04-19|Sumant|Peac|F|1985-02-18
--10|10010|1963-06-01|Duangkaew|Piveteau|F|1989-08-24
select * from Employees
go

declare @a int = 2;
declare @b int = 5;
select * from Employees
order by Id asc offset (@a - 1) * @b rows fetch next @b rows only
go

--5. ������30�֣�
--�������յ�M��˾�Ŀ�����Լ���⿪��һ��OA�칫�Զ���ϵͳ����ϵͳ�Ĳ��ֹ��ܼ�������������Ľ������ ��

--��1��M��˾������ҵ�񲿡��߻������������š�������Ϣ�������źš������������ܡ���ϵ�绰������ţ�
--ÿ������ֻ��һ�����ܣ�ֻ������������ŵĹ����������ܲ���Ա����ϵ��Ա���ţ�һ�������ж���Ա����ÿ��Ա�������ҽ�����һ�����š�

--��2��Ա����Ϣ����Ա���š�������ְλ����ϵ��ʽ��н�ʡ�ְλ�������ܡ�ҵ��Ա�� �߻�Ա�ȡ�
--ҵ��Ա���������û����룬����������־��һ��ҵ��Ա������������û����룬��һ���û�����ֻ����һ��ҵ��Ա������

--��3���û���Ϣ�����û��š��û����������˺š��绰����ϵ��ַ���û���Ψһ��ʶ�û���Ϣ�е�ÿһ��Ԫ�顣

--��4���û�������Ϣ��������š��û��š��������ڡ��������λ��������ص㡢Ԥ���������־
--��������־�У������루Ĭ�ϣ����Ѿܾ����������������������ѹ��ڣ��������Ψһ��ʶ�û�������Ϣ�е�ÿһ��Ԫ�飬��һ���û������ύ������룬��һ���û�����ֻ��Ӧһ���û��š�

--��5���߻�������Ϊ���������û������ƶ�����߻����񡣲߻������������š�������ϸ��Ҫ�����ʱ��.�����Ψһ��ʶ�߻������ÿһ��Ԫ�顣
-- һ���߻�����ֻ��Ӧһ�����������û����룬��һ���߻�������ɶ����߻�Ա����ִ�У���һ���߻�Ա���Բ���ִ�У����޸Ĳ߻�����

--�}Ŀ1��10�֣���ʹ��PowerDesigner����ƿ���ʹ�õ�SqlServer���ݿ�����ģ�ͣ�Ҫ���������ƺ�Ӣ�����������Ӣ�Ĳ�֪���Ŀ��Խ�������������������ϵ׼ȷ��
--������ƺõ�ģ������sql��䣬����һ����ΪOADatabase�����ݿ⣬���ã�

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Business') and o.name = 'FK_BUSINESS_REFERENCE_USERS')
alter table Business
   drop constraint FK_BUSINESS_REFERENCE_USERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('employees') and o.name = 'FK_EMPLOYEE_REFERENCE_DEPARTME')
alter table employees
   drop constraint FK_EMPLOYEE_REFERENCE_DEPARTME
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Business')
            and   type = 'U')
   drop table Business
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Department')
            and   type = 'U')
   drop table Department
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Users')
            and   type = 'U')
   drop table Users
go

if exists (select 1
            from  sysobjects
           where  id = object_id('employees')
            and   type = 'U')
   drop table employees
go

/*==============================================================*/
/* Table: Business                                              */
/*==============================================================*/
create table Business (
   Use_UserId           int                  null,
   BusinessNo           varchar(40)          null,
   UserID               int                  null,
   condate              datetime             null,
   condays              varchar(20)          null,
   connp                int                  null,
   consite              varchar(40)          null,
   money                money                null,
   state1                varchar(20)       default('������')     null
)
go

/*==============================================================*/
/* Table: Department                                            */
/*==============================================================*/
create table Department (
   Departmentid         int                  not null,
   Departmentname       varchar(20)          null,
   functionary          varchar(20)          null,
   phone                varchar(40)          null,
   email                varchar(80)          null,
   constraint PK_DEPARTMENT primary key (Departmentid)
)
go

/*==============================================================*/
/* Table: Users                                                 */
/*==============================================================*/
create table Users (
   UserId               int                  not null,
   Username             varchar(20)          null,
   bankno               varchar(20)          null,
   Userphone            varchar(20)          null,
   Usersite             varchar(60)          null,
   constraint PK_USERS primary key (UserId)
)
go

/*==============================================================*/
/* Table: employees                                             */
/*==============================================================*/
create table employees (
   empid                int                  not null,
   empname              varchar(20)          null,
   post                 varchar(20)          null,
   empphone             varchar(20)          null,
   emppay               varchar(20)          null,
   Departmentid         int                  null,
   constraint PK_EMPLOYEES primary key (empid)
)
go

alter table Business
   add constraint FK_BUSINESS_REFERENCE_USERS foreign key (Use_UserId)
      references Users (UserId)
go

alter table employees
   add constraint FK_EMPLOYEE_REFERENCE_DEPARTME foreign key (Departmentid)
      references Department (Departmentid)
go

--��Ŀ2��10�֣���Ϊ�˱�֤���û�������飬������־Ϊ �����룬�����һ�������������û�������Ϣ���������ݵ�ʱ��
--�����ж���Ĭ��������־�Ƿ�Ϊ�������룬����ǣ������账�������������룬������������¸���������־Ϊ�������룻
insert into "employeeinformationtable"(job)values('主管'),('业务员'),('策划员')
go

create trigger tr_user_jobnumber
on employeeinformationtable
for
instead of insert
as
begin
	INSERT INTO Userapplicationinformationtable
end

--��Ŀ3��10�֣�����ҵ��Ա�����û������ʱ����Ҫ����ǰ�����ļ�¼��������־�޸�Ϊ����������
--ͬʱ����������״̬Ϊ�������룬�һ��������ѳ����ļ�¼�е�������־����Ϊ���ѹ��ڣ������һ���洢������������ҵ��
go

create proc proc_business
@Toacceptthelogo nvarchar(80),
@state nvarchar(80),
@dateofaconference datetime
as
begin
	if ()
	begin
		set @Toacceptthelogo='已受理'
		set @state='已申请'
		set @dateofaconference='已过期'
	end
end
go
